I am trying to create three column charts from the Excel sheet data using DXL script as explained below. Microsoft Office Excel 2007 has been installed in my computer.
I select the values from ROW1 & ROW2 and generate column chart which is named as ROW1&2. This works fine.
I select the values from ROW3 & ROW4 and generate column chart. Chart2(named as ROW3&4) is generated but it takes the value from ROW1 & ROW2 instead ROW3 & ROW4.
The same problem happens with the chart3(named as ROW5&6) as well. It only takes the values from ROW1 & ROW2 instead ROW5 & ROW6.
Please find the code below. Thanks in advance for your help.
//Create Ole Auto arguements
OleAutoArgs AutoArgs = create()
OleAutoObj objExcel = null
OleAutoObj objWorkBooks = null
OleAutoObj objWorkBook = null
OleAutoObj objSheets = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objRange = null
OleAutoObj objCharts = null
OleAutoObj objChart = null
OleAutoObj objActvChart = null
int RangeVal = 0
string name = null
//Start Excel application
objExcel = oleCreateAutoObject "Excel.Application"
olePut(objExcel, "visible", true)
//Get access to workbooks
oleGet(objExcel,"Workbooks",objWorkBooks)
//Add a new workbook
put(AutoArgs ,1)
oleMethod(objWorkBooks, "Add", AutoArgs , objWorkBook )
clear AutoArgs
//Activate the current sheet
oleGet(objWorkBook ,"Activesheet",objSheet)
//Rename the active sheet
name = "REPORT"
olePut(objSheet,"Name",name)
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A1:F2")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 10
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objExcel ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objExcel ,"Activechart", objActvChart )
name = "ROW1&2"
olePut(objActvChart , "name", name )
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A3:F4")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 20
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objExcel ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objExcel ,"Activechart", objActvChart )
name = "ROW3&4"
olePut(objActvChart , "name", name )
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A5:F6")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 30
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objExcel ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objExcel ,"Activechart", objActvChart )
name = "ROW5&6"
olePut(objActvChart , "name", name )
//Delete or close all variables
delete AutoArgs
oleCloseAutoObject objExcel
oleCloseAutoObject objWorkBooks
oleCloseAutoObject objWorkBook
oleCloseAutoObject objSheets
oleCloseAutoObject objSheet
oleCloseAutoObject objCell
oleCloseAutoObject objRange
oleCloseAutoObject objCharts
oleCloseAutoObject objChart
oleCloseAutoObject objActvChart
Kavi28 - Thu May 15 06:07:51 EDT 2014 |
Re: Column chart is not taking the right inputs
Kavi28 - Fri May 16 07:56:04 EDT 2014 llandale - Thu May 15 16:46:07 EDT 2014
Don't know. Not good at this.
I notice that after your code runs, A1:F5 is still selected on the original REPORT.
It seems to me that when you select range "A3:F4", the chart sheet "Row1&2" is currently active, so "A3:F4" applies to that instead of the original sheet. If so, seems like you need to "activate" objSheet before the 3:4 sequence and also before the 5:6 sequence.
-louie
Hello Mr.Louie,
Thanks for your hint and yes, it works as expected.
I attach the latest version of the code for reference.
//Create Ole Auto arguements
OleAutoArgs AutoArgs = create()
OleAutoObj objExcel = null
OleAutoObj objWorkBooks = null
OleAutoObj objWorkBook = null
OleAutoObj objSheets = null
OleAutoObj objSheet = null
OleAutoObj objCell = null
OleAutoObj objRange = null
OleAutoObj objCharts = null
OleAutoObj objChartsCnt = null
OleAutoObj objChart = null
OleAutoObj objActvChart = null
int RangeVal = 0
string name = null
//Start Excel application
objExcel = oleCreateAutoObject "Excel.Application"
olePut(objExcel, "visible", true)
//Get access to workbooks
oleGet(objExcel,"Workbooks",objWorkBooks)
//Add a new workbook
put(AutoArgs ,3)
oleMethod(objWorkBooks, "Add", AutoArgs , objWorkBook )
clear AutoArgs
//Activate the current sheet
oleGet(objWorkBook ,"Activesheet",objSheet)
//Rename the active sheet
name = "REPORT"
olePut(objSheet,"Name",name)
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A1:F2")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 10
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objWorkBook ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objWorkBook ,"Activechart", objActvChart )
name = "ROW1&2"
olePut(objActvChart , "name", name )
//Activate work sheet
oleMethod (objSheet, "Activate")
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A3:F4")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 20
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objWorkBook ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objWorkBook ,"Activechart", objActvChart )
name = "ROW3&4"
olePut(objActvChart , "name", name )
//Activate work sheet
oleMethod (objSheet, "Activate")
//Select a range in excel sheet and update it with a value
put(AutoArgs, "A5:F6")
oleGet(objSheet,"Range",AutoArgs ,objRange )
oleMethod (objRange, "Select")
RangeVal = 30
olePut(objRange, "value", RangeVal )
clear AutoArgs
//Add a column chart for the selected range
oleGet(objWorkBook ,"Charts", objCharts )
oleMethod(objCharts , "Add")
oleGet(objWorkBook ,"Activechart", objActvChart )
name = "ROW5&6"
olePut(objActvChart , "name", name )
//Delete or close all variables
delete AutoArgs
oleCloseAutoObject objExcel
oleCloseAutoObject objWorkBooks
oleCloseAutoObject objWorkBook
oleCloseAutoObject objSheets
oleCloseAutoObject objSheet
oleCloseAutoObject objCell
oleCloseAutoObject objRange
oleCloseAutoObject objCharts
oleCloseAutoObject objChart
oleCloseAutoObject objActvChart
|
Re: Column chart is not taking the right inputs
O.Wilkop - Wed Nov 08 05:45:36 EST 2017 c-nolan - Tue Nov 07 11:54:17 EST 2017
Hey!
It's been hard to find resources on this elsewhere. I found the example above pretty useful. However, I'm having trouble finding resources on how to access other properties of these objects. I've looked at the excel object sheet on but the examples are all in VBA. For example, using the code above, how would I name the columns? Instead of having them be numbered sequentially.
Thanks!
You can roughly translate vba code to dxl, see https://www.ibm.com/developerworks/community/forums/html/topic?id=adbb257e-05e0-4f7e-8124-0a30e4f053ec where I posted a example on how to modify/add a chart title and the equivalent VBA code.
Properties you usually get with OleGet, methods use OleMethod, named method parameters get added with put(args, "name", "value"), unnamed ones you can just add with put(args, "value"). Assigning properties works with olePut.
Using the "record makro" function in excel I figured out that changing the column text works with:
ActiveChart.SeriesCollection(1).Name = "=""NewName"""
which in dxl could be something like (untested):
//... declare variables, open workbook, declare args object, etc
oleGet(objWorkBook ,"Activechart", objActvChart );
OleAutoObj objSeriesCollection;
clear(args);
put(args, 1);
oleMethod(objActvChart, "SeriesCollection", args, objSeriesCollection);
olePut(objSeriesCollection, "Name", "=\"NewName\"");
|